﻿using Dapper;
using Dapper.Contrib.Extensions;
using Gx.Common;
using Gx.Common.Core;
using Gx.Dal.Shared;
using Gx.Model.Entity;
using Gx.Model.Enums;
using Gx.Model.ViewModel;
using System.Collections.Generic;
using System.Linq;
namespace Gx.Dal.Manage
{
    public class LKZeroWarehouseContractDal : BaseDal<LKZeroWarehouseContract>
    {

        /// <summary>
        /// 名称是否存在
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public bool AnyName(string name)
        {
            using (var conn = new DbContext())
            {
                var total = conn.Connection.QueryFirst<int>("SELECT COUNT(1) FROM LK_ZeroWarehouseContract WHERE IsDeleted=0 AND Name=@name", new { name });
                return total > 0;
            }
        }

        /// <summary>
        /// 获取最大编号
        /// </summary>
        /// <returns></returns>
        public string GetMaxLKZeroWarehouseContractCode()
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.QueryFirst<string>(@"SELECT MAX(CAST(SUBSTRING(ContractNo, 2,LEN(ContractNo)-1) AS int)) AS MaxContractNumber
FROM LK_ZeroWarehouseContract
WHERE ISNUMERIC(SUBSTRING(ContractNo, 2, LEN(ContractNo) - 1)) = 1");
            }
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public PagedList<LKZeroWarehouseContract> GetLKZeroWarehouseContractPageList(LKZeroWarehouseContractInput input)
        {
            string sql = @"SELECT [Id]
      ,[ContractNo]
      ,[CustomerId]
      ,[CustomerName]
      ,[ContractTime]
      ,[BeginTime]
      ,[EndTime]
      ,[Tonnage]
      ,[IsByTon]
	  ,[ChargingUnit]
      ,CASE [ChargingUnit]
	  WHEN 1 THEN '按吨'
	  WHEN 2 THEN '按件'
	  WHEN 3 THEN '按托盘/面积'
	  WHEN 4 THEN '按板'
	  ELSE '' END AS ChargingUnitName
      ,[ColdUnitPrice]
      ,[ColdAlgorithm]
	  ,CASE [ColdAlgorithm]
	  WHEN 1 THEN '按周期收取'
	  WHEN 2 THEN '先按周期后按天收取'
	  WHEN 3 THEN '按每天收取'
	  WHEN 4 THEN '按合同收取'
	  WHEN 5 THEN '按天后收，月结'
	  WHEN 6 THEN '按天后收，不月结'
	  ELSE '' END AS ColdAlgorithmName
      ,[Cycle]
      ,[Remark]
      ,[IsValid]
      ,[CreateTime]
      ,[CreateUserId]
      ,[CreateUser]
      ,[ModifyUserId]
      ,[ModifyUser]
      ,[ModifyTime]
      ,[IsDeleted],[Days]
,[CompanyId],Status
  FROM [dbo].[LK_ZeroWarehouseContract] WHERE IsDeleted=0 @WHERE";
            List<string> conditions = new List<string>();
            //判断input的属性是否为空，如果不为空则添加到conditions中
            if (!string.IsNullOrWhiteSpace(input.ContractNo))
            {
                input.ContractNo = $"%{input.ContractNo}%";
                conditions.Add("ContractNo LIKE @ContractNo");
            }
            if (input.BeginTime.HasValue)
            {
                conditions.Add("CreateTime >= @BeginTime");
            }
            if (input.EndTime.HasValue)
            {
                conditions.Add("CreateTime <= @EndTime");
            }
            if (input.CustomerId.HasValue)
            {
                conditions.Add("CustomerId = @CustomerId");
            }
            if (input.ModifyUserId.HasValue)
            {
                conditions.Add("ModifyUserId = @ModifyUserId");
            }
            if (input.IsValid.HasValue)
            {
                conditions.Add("IsValid = @IsValid");
            }

            if (UserContext.RoleId != (int)Roles.管理员) 
            {
                conditions.Add($"CompanyId={UserContext.CompanyId}");
            }

            string condition = string.Empty;
            if (conditions.Any())
            {
                condition = $" AND {string.Join(" AND ", conditions)}";
            }
            sql = !string.IsNullOrWhiteSpace(condition) ? sql.Replace("@WHERE", condition) : sql.Replace("@WHERE", "");

            using (var conn = new DbContext())
            {
                return conn.Connection.GetPagedList<LKZeroWarehouseContract>(sql, "CreateTime Desc", input);
            }
        }


        /// <summary>
        /// 新增零仓合同
        /// </summary>
        /// <param name="contract"></param>
        /// <param name="costs"></param>
        /// <returns></returns>
        public bool AddLKZeroWarehouseContract(LKZeroWarehouseContract contract,List<LKIncidentals> costs)
        {
            return UseTransaction((conn, trans) => 
            {
                var result = conn.Insert(contract, trans);

                if (result == 0) return false;

                if (costs != null && costs.Any())
                {
                    costs.ForEach(item => item.ContractId = contract.Id);
                    conn.Insert(costs, trans);
                }

                return result > 0;
            });
        }

        /// <summary>
        /// 编辑零仓合同
        /// </summary>
        /// <param name="contract"></param>
        /// <param name="costList"></param>
        /// <param name="deletes"></param>
        /// <returns></returns>
        public bool EditLKZeroWarehouseContract(LKZeroWarehouseContract contract, List<LKIncidentals> costList, List<int> deletes)
        {
            return UseTransaction((conn, trans) =>
            {
                conn.Update(contract, trans);

                if (costList != null && costList.Any())
                {
                    costList.ForEach(item => item.ContractNo = contract.ContractNo);
                    var adds = costList.Where(c => c.Id == 0).ToList();
                    var updates = costList.Where(c => c.Id > 0).ToList();

                    if (updates.Any())
                    {
                        conn.Update(updates,trans);
                    }
                    if (adds.Any())
                    {
                        adds.ForEach(item =>
                        {
                            item.ContractId = contract.Id;
                            item.ContractNo= contract.ContractNo;
                            item.ContractType = (int)BillType.ZeroWarehouseContract;
                            item.CreateUser = LoginHelper.UserName();
                            item.CreateUserId = LoginHelper.GetUserId();
                        });
                        conn.Insert(adds, trans);
                    }
                }

                if (deletes != null && deletes.Any())
                {
                    conn.Execute("UPDATE LK_Incidentals SET IsDeleted=1 WHERE Id IN @deletes AND ContractType=1", new { deletes }, trans);
                }

                return true;
            });
        }
        /// <summary>
        /// 删除零仓合同
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteLKZeroWarehouseContract(int id)
        {
            return UseTransaction((conn, trans) =>
            {

                conn.Execute("UPDATE LK_ZeroWarehouseContract SET IsDeleted=1 WHERE Id=@id", new { id }, trans);
                conn.Execute("UPDATE LK_Incidentals SET IsDeleted=1 WHERE ContractId=@id AND ContractType=1", new { id }, trans);
                return true;
            });
        }

        public List<LKZeroWarehouseContract> GetZeroContractByBalance()
        {
            using (var conn = new DbContext())
            {
                return conn.Connection.Query<LKZeroWarehouseContract>(@"SELECT DISTINCT e.ContractNo,c.ColdUnitPrice,c.Days
FROM dbo.LK_WarehouseEntryDetail d WITH(NOLOCK)
JOIN dbo.LK_WarehouseEntry e WITH(NOLOCK) ON d.EntryId=e.Id
JOIN dbo.LK_ZeroWarehouseContract c WITH(NOLOCK) ON e.ContractNo=c.ContractNo
LEFT JOIN (SELECT GoodsNo,SUM(OutNumber) AS OutNumber,SUM(TonWeight) AS TonWeight FROM dbo.LK_WarehouseOutDetail WITH(NOLOCK)
WHERE IsDeleted=0
GROUP BY GoodsNo) AS  od  ON d.GoodsNo=od.GoodsNo
WHERE d.IsDeleted=0 AND e.IsDeleted=0 AND e.RentalWarehouseType=1 AND (od.GoodsNo IS NULL OR d.Number>od.OutNumber)").ToList();
            }
        }
    }
}

